﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using DBUtility;
using MySql.Data.MySqlClient;

namespace DAL
{
    public class DTaxiWarming
    {
        /// <summary>
        /// 增加一条数据
        /// </summary>
        public bool Add(Model.TaxiWarmingInfo model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("insert into twarming(");
            strSql.Append("PhoneNumber,Warmingstate,WarmingTime)");
            strSql.Append(" values (");
            strSql.Append("@PhoneNumber,@Warmingstate,@WarmingTime)");
            MySqlParameter[] parameters = {
					new MySqlParameter("@PhoneNumber", MySqlDbType.VarChar,45),
					new MySqlParameter("@Warmingstate", MySqlDbType.VarChar,45),
					new MySqlParameter("@WarmingTime", MySqlDbType.DateTime)
					};
            parameters[0].Value = model.PhoneNumber;
            parameters[1].Value = model.WarmingState;
            parameters[2].Value = model.Datetime;
       
            int rows = DbHelperMySQL.ExecuteSql(strSql.ToString(), parameters);
            if (rows > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }
        /// <summary>
        /// 更新一条数据
        /// </summary>
        public bool Update(Model.TaxiWarmingInfo model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("update twarming set ");
            strSql.Append("PhoneNumber=@PhoneNumber,");
            strSql.Append("Warmingstate=@Warmingstate,");
            strSql.Append("WarmingTime=@WarmingTime");            
            strSql.Append(" where id=@id");
            MySqlParameter[] parameters = {
					new MySqlParameter("@PhoneNumber", MySqlDbType.VarChar,45),	
					new MySqlParameter("@Warmingstate", MySqlDbType.VarChar,45),
					new MySqlParameter("@WarmingTime", MySqlDbType.DateTime),
					new MySqlParameter("@id", MySqlDbType.Int32,10)};
            parameters[0].Value = model.PhoneNumber;
            parameters[1].Value = model.WarmingState;
            parameters[2].Value = model.Datetime;                        
            parameters[3].Value = model.Id;

            int rows = DbHelperMySQL.ExecuteSql(strSql.ToString(), parameters);
            if (rows > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }

        /// <summary>
        /// 删除一条数据
        /// </summary>
        public bool Delete(int id)
        {

            StringBuilder strSql = new StringBuilder();
            strSql.Append("delete from twarming ");
            strSql.Append(" where id=@id");
            MySqlParameter[] parameters = {
					new MySqlParameter("@id", MySqlDbType.Int32)
};
            parameters[0].Value = id;

            int rows = DbHelperMySQL.ExecuteSql(strSql.ToString(), parameters);
            if (rows > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }        

        /// <summary>
        /// 得到一个对象实体
        /// </summary>
        public Model.TaxiWarmingInfo GetModel(int id)
        {

            StringBuilder strSql = new StringBuilder();
            strSql.Append("select id,PhoneNumber,Warmingstate,WarmingTime from twarming ");
            strSql.Append(" where id=@id");
            MySqlParameter[] parameters = {
					new MySqlParameter("@id", MySqlDbType.Int32)};
            parameters[0].Value = id;

            Model.TaxiWarmingInfo model = new Model.TaxiWarmingInfo();
            DataSet ds = DbHelperMySQL.Query(strSql.ToString(), parameters);
            if (ds.Tables[0].Rows.Count > 0)
            {
                if (ds.Tables[0].Rows[0]["id"] != null && ds.Tables[0].Rows[0]["id"].ToString() != "")
                {
                    model.Id = int.Parse(ds.Tables[0].Rows[0]["id"].ToString());
                }
                if (ds.Tables[0].Rows[0]["PhoneNumber"] != null && ds.Tables[0].Rows[0]["PhoneNumber"].ToString() != "")
                {
                    model.PhoneNumber = ds.Tables[0].Rows[0]["PhoneNumber"].ToString();
                }
                if (ds.Tables[0].Rows[0]["Warmingstate"] != null && ds.Tables[0].Rows[0]["Warmingstate"].ToString() != "")
                {
                    model.WarmingState = ds.Tables[0].Rows[0]["Warmingstate"].ToString();
                }
                if (ds.Tables[0].Rows[0]["WarmingTime"] != null && ds.Tables[0].Rows[0]["WarmingTime"].ToString() != "")
                {
                    model.Datetime = DateTime.Parse(ds.Tables[0].Rows[0]["WarmingTime"].ToString());
                }                 
                return model;
            }
            else
            {
                return null;
            }
        }

        /// <summary>
        /// 获得数据列表
        /// </summary>
        public DataSet GetList(string strWhere)
        {
            //StringBuilder strSql = new StringBuilder();
            //strSql.Append("select * ");
            //strSql.Append(" FROM twarming ");
            //if (strWhere.Trim() != "")
            //{
            //    strSql.Append(" where " + strWhere);
            //}
            //return DbHelperMySQL.Query(strSql.ToString());

            StringBuilder strSql = new StringBuilder();
            strSql.Append("SELECT t.Id,t.PhoneNumber,t.Warmingstate,t.WarmingTime,");
            strSql.Append("tdriver.Name,ttaxi.TaxiNo,tCompany.CompanyName ");
            strSql.Append("FROM taxidispatchsystem.twarming t ");
            strSql.Append("left join tdriver on t.PhoneNumber = tdriver.PhoneNumber ");
            strSql.Append("left join ttaxi  on tdriver.TaxiId = ttaxi.Id ");
            strSql.Append("left join tcompany on ttaxi.CompanyId = tcompany.Id;");   
            if (strWhere.Trim() != "")
            {
                strSql.Append(" where " + strWhere);
            }
            return DbHelperMySQL.Query(strSql.ToString());

        }

        public DataSet GetListByPage(int page, int pagesize, string strWhere)
        {
            //StringBuilder strSql = new StringBuilder();
            //strSql.Append("select  Id,PhoneNumber,Warmingstate,WarmingTime");                        
            //strSql.Append(" From twarming ");                        
            //if (strWhere.Trim() != "")
            //{
            //    strSql.Append("where ");
            //    strSql.Append(strWhere);                
            //}

            StringBuilder strSql = new StringBuilder();
            strSql.Append("SELECT t.Id,t.PhoneNumber,t.Warmingstate,t.WarmingTime,");
            strSql.Append("tdriver.Name,ttaxi.TaxiNo,tCompany.CompanyName ");
            strSql.Append("FROM twarming t ");
            strSql.Append("left join tdriver on t.PhoneNumber = tdriver.PhoneNumber ");
            strSql.Append("left join ttaxi  on tdriver.TaxiId = ttaxi.Id ");
            strSql.Append("left join tcompany on ttaxi.CompanyId = tcompany.Id ");
            if (strWhere.Trim() != "")
            {
                strSql.Append("where ");
                strSql.Append(strWhere);
            }

            strSql.Append(" order by WarmingTime desc ");
            strSql.Append("limit ");
            strSql.Append((page - 1) * pagesize);
            strSql.Append(",");
            strSql.Append(pagesize);
            DataSet ds = DbHelperMySQL.Query(strSql.ToString());
            return ds;

        }

        public int GetRecordCount(string strWhere)
        {
            //StringBuilder strSql = new StringBuilder();
            //strSql.Append("select count(*)");
            //strSql.Append(" From twarming ");
            StringBuilder strSql = new StringBuilder();
            strSql.Append("SELECT count(*) ");           
            strSql.Append("FROM twarming t ");
            strSql.Append("left join tdriver on t.PhoneNumber = tdriver.PhoneNumber ");
            strSql.Append("left join ttaxi  on tdriver.TaxiId = ttaxi.Id ");
            strSql.Append("left join tcompany on ttaxi.CompanyId = tcompany.Id ");
 
            if (strWhere.Trim() != "")
            {
                strSql.Append("where ");
                strSql.Append(strWhere);
            }            
            DataSet ds = DbHelperMySQL.Query(strSql.ToString());
            int count = 0;
            if(ds!=null && ds.Tables[0].Rows.Count>0)
            {
                count = int.Parse(ds.Tables[0].Rows[0][0].ToString());
            }
            return count;
        }

    }
}
